﻿//------------------------------------------------------------------------------
//  
//------------------------------------------------------------------------------
#region Imported Namespaces
using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Collections;

using SSRS;
using PHSRAG.Insight21.Reports;
using PHSRAG.Insight21.InsightUtilities;
using PHSRAG.Insight21.SearchContext;
using AgreementsConstants = PHSRAG.Insight21.InsightUtilities.Constants.AgreementsConstants;
using BudgetConstants = PHSRAG.Insight21.InsightUtilities.Constants.BudgetInfoConstants;
using PHSRAG.Insight21.Agreements;
using PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using UserContext = PHSRAG.Insight21.Context.UserContext;
using PHSRAG.Insight21.InsightWebControls;
#endregion

namespace PHSRAG.Insight21.Agreements.Reports {

    
    public class sqlBudgetOverviewReport : sqlReport
    {

        private const string cSummaryReportName = "Agmt_BudgetOverview";
        private const string cDetailReportName = "Agmt_BudgetDetails";
        public const string cSummaryReportTitle = "Budget Overview";
        public const string cDetailReportTitle = "Budget Detail";
        private const string IsDetailsFlagKey = "isDetails";
        private const string SearchCriteriaVersionKey = "searchCriteriaVersionKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string UserOrderVersionKey = "userOrderVersionKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string FundNumberKey = "fundNumberKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string RelatedFundNumberKey = "relatedFundNumberKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string CumLevelKey = "cumLevelKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string MonthEndingDateKey = "monthEndingDateKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const string BudgetYearKey = "agreementIDKey_09EA22DD-144C-40E0-BED5-22B334A59A03";
        private const int DefaultBudgetYear = 0;
        private const string SelectPrompt = "-- Select --";

        //Constact for Salary overview
        private const string AsofDateKey = "glPostAsOfDate";

        public sqlBudgetOverviewReport(string pageUrl): base(pageUrl)
        {
            doHashtable = true;
            doCustomExcelReport = true;
        }

        protected override string reportTitle
        {
            get
            {
                return (IsDetail) ? cDetailReportTitle : cSummaryReportTitle;
            }
            set
            {
                base.reportTitle = value;
            }
        }

        public override string ReportName {
            get
            {
                return IsDetail ? cDetailReportName:cSummaryReportName;
            }

        }

        public override Hashtable setParameterHTable(ref string rptName)
        {
            rptName = ReportName;
            reportTitle = reportTitle;

            reportSource = PrepareTablesForDownload();

            searchCriteria.Clear();
            reportCriteria = AddRefreshDatesToCriteria();

            return base.setParameterHTable(ref rptName);
        }

        /// <summary>
        /// Create a new DataSet for download to excel and pdf by removing the child rows from 
        /// the master table and adding a column in the account table as below:
        /// <list type="bullet">
        /// <item><description>Get the tables collection from the cached object depending on the context</description></item>
        /// <item><description>Create a new dataset and copy all the tables from the table collection. This is
        /// done so that removing rows from the master table does not affect the dataset of the cached object</description></item>
        /// <item><description>Get the master(category) and the details(accounts) table from the new dataset</description></item>
        /// <item><description>Add a column to accounts table which will hold the integer value for the account type. This is
        /// done to facilitate generation of formulas for estimated expenses based on the account type in xslt.</description></item>
        /// <item><description>Loop through detail(accounts) rows, update the value for account type and select rows
        /// in master table matching the category id and account number of the current row.</description></item>
        /// <item><description>If matching row is found remove it from the expanded rows</description></item>
        /// <item><description>Accept the changes in the new dataSet and return it to the calling method</description></item>
        /// </list>  
        /// </summary>
        /// <returns>DataSet having the right data for downloads</returns>
        private DataSet PrepareTablesForDownload()
        {
            DataTableCollection budgetInfoTables = (IsDetail) ?
                BudgetDetailsInstance.GetDataContainer().Tables :
                BudgetOverviewInstance.GetDataContainer().Tables;          
           
            
             
            DataSet trimmed = new DataSet();

            foreach (DataTable dataTable in budgetInfoTables)
                trimmed.Tables.Add(dataTable.Copy());

            /*If Data is being prepared for budget details report, add salary details data tables*/
            if (IsDetail)
            {
                //Datatables, contails salary details data and SalaryTotal
                DataTable dtSalaryDetails = SalaryDistributions.GetDataContainer().Tables["original"];

                DataTable dtTotalSalary = SalaryDistributions.GetDataContainer().Tables[SalaryDistributions.TotalsTableName];

                //adding salary distribution  datatables
                trimmed.Tables.Add(dtTotalSalary.Copy());
                trimmed.Tables.Add(dtSalaryDetails.Copy());
            }
           
           

            DataTable category = trimmed.Tables[BudgetConstants.CategoryTableName];
            DataTable accountTable = trimmed.Tables[AgreementsConstants.AccountsTableName];

            DataRowCollection accountRows = accountTable.Rows;

            foreach (DataRow account in accountRows)
            {
                DataRow[] expandedRows = category.Select(String.Format("{0} = {1} AND {2} = '{3}'",
                            BudgetConstants.CategoryIDColumnName,
                    account[BudgetConstants.CategoryIDColumnName],
                            BudgetConstants.AccountIDColumnName,
                    account[BudgetConstants.AccountIDColumnName]));

                foreach (DataRow expandedRow in expandedRows)
                    category.Rows.Remove(expandedRow);
            }
            trimmed.AcceptChanges();

           
            return trimmed;
        }

        /// <summary>
        /// Get and Set the flag which specifies the context of the page (Overview/Details)
        /// </summary>okie
        /// 
        private bool IsDetail
        {
            get
            {
                Hashtable incomingHashTable = (Hashtable)pageCachePolicy.GetCachedObject(AgreementsConstants.AgreementDetailsKey);
                return (incomingHashTable == null || incomingHashTable[Keys.FundNumberKey] == null)? false : true;             
            }
        }

        /// <summary>
        /// Get the BudgetOverview instance with its data loaded
        /// Check the cache if it already contains an instance of BudgetOverview
        /// If the cache does not contain an instance of BudgetOveview or if the
        /// searchCriteria version has changed or if user orders have changed
        /// load a new instance of budgetoverview with the active search criteria.
        /// Cache the newly instantiated object and return
        /// </summary>
        private PHSRAG.Insight21.Agreements.BudgetOverview BudgetOverviewInstance
        {
            get
            {
                int searchVersion = searchCriteria.Version;

                PHSRAG.Insight21.Agreements.BudgetOverview budgetOverview =
                    Policy.ResultsCache.GetObject(cachePolicy, PHSRAG.Insight21.Agreements.BudgetOverview.CacheKey) as PHSRAG.Insight21.Agreements.BudgetOverview;

                bool includeUserOrdersCriteria = true; //(Request.Form[overviewSearch.GetIncludeUserOrdersID()] != null);

                // pages that manipulate user orders maintain the version of user
                // orders in session under a well known key
                object o = pageCachePolicy.GetCachedObject(Keys.UserOrdersVersionKey);
                int userOrdersVersion = o == null ? 0 : (int)o;

                // instigate a search if user orders have changed also
                if (budgetOverview == null ||
                    (includeUserOrdersCriteria && UserOrdersVersion != userOrdersVersion) ||
                    ((searchContext != null) && (searchVersion != SearchCriteriaVersion)))
                {
                    SearchCriteriaVersion = searchVersion;
                    UserOrdersVersion = userOrdersVersion;

                    if (budgetOverview == null)
                    {
                        budgetOverview = new PHSRAG.Insight21.Agreements.BudgetOverview();
                    }

                    budgetOverview.Load(Utility.DataAccess.ConnectionString, Session.SessionID, searchCriteria);

                    Policy.ResultsCache.SetObject(cachePolicy, PHSRAG.Insight21.Agreements.BudgetOverview.CacheKey, budgetOverview);
                }
                return budgetOverview;
            }
        }

        /// <summary>
        /// Get the BudgetDetails instance with its data loaded
        /// Check the cache if it already contains an instance of BudgetDetails
        /// If the cache does not contain an instance of BudgetDetails load a new instance of budgetdetails
        /// with the domainUserID,FundNumber,MonthEndingDate and BudgetYear.
        /// Cache the newly instantiated object and return
        /// </summary>
        private PHSRAG.Insight21.Agreements.BudgetDetails BudgetDetailsInstance
        {
            get
            {
                PHSRAG.Insight21.Agreements.BudgetDetails budgetDetails =
                Policy.ResultsCache.GetObject(cachePolicy, PHSRAG.Insight21.Agreements.BudgetDetails.CacheKey) as PHSRAG.Insight21.Agreements.BudgetDetails;

                if (budgetDetails == null)
                {
                    budgetDetails = new PHSRAG.Insight21.Agreements.BudgetDetails();
                    string fundNumber = (RelatedFundNumber.Equals(string.Empty)) ? FundNumber : RelatedFundNumber;
                    //budgetDetails.Load(Utility.DataAccess.ConnectionString
                    //    , ((UserContext)cachePolicy.GetCachedObject(UserContext.CacheKey)).DomainUserId
                    //    , fundNumber, MonthEndingDate, BudgetYear, cumLevel.SelectedValue, budgetList.SelectedItem.Value);


                    Policy.ResultsCache.SetObject(cachePolicy, PHSRAG.Insight21.Agreements.BudgetDetails.CacheKey, budgetDetails);
                }

                return budgetDetails;
            }
        }

        /// <summary>
        /// Get and Set the search criteria version
        /// </summary>
        private int SearchCriteriaVersion
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[SearchCriteriaVersionKey] == null) ? 0 : (int)h[SearchCriteriaVersionKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[SearchCriteriaVersionKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }

        /// <summary>
        /// Get and Set the user order version that signifies whether
        /// user orders have been added/deleted/edited so as to instigate
        /// a database it to get fresh data.
        /// </summary>
        private int UserOrdersVersion
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[UserOrderVersionKey] == null) ? 0 : (int)h[UserOrderVersionKey];
            }
            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[UserOrderVersionKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }

        /// <summary>
        /// Get and Set the value of the cumulative level which is used to load the budget details data
        /// </summary>
        private int CumLevel
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[CumLevelKey] == null) ? 0 : (int)h[CumLevelKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[CumLevelKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }

        /// <summary>
        /// Get and Set the fund number which brought us to the budget details page.
        /// </summary>
        private string FundNumber
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[FundNumberKey] == null) ? string.Empty : (string)h[FundNumberKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[FundNumberKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }

        /// <summary>
        /// Get and Set the fund number which is used for the search in the Budget Details report
        /// </summary>
        private string RelatedFundNumber
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[RelatedFundNumberKey] == null) ? string.Empty : (string)h[RelatedFundNumberKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[RelatedFundNumberKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        } 

        /// <summary>
        /// Get and Set the month ending date used for the Budget details load
        /// </summary>
        private string MonthEndingDate
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[MonthEndingDateKey] == null) ? DateTime.Now.ToShortDateString() :
                    (string)h[MonthEndingDateKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[MonthEndingDateKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }

        /// <summary>
        /// Get and set the BudgetYear used for the Budget details load
        /// </summary>
        private int BudgetYear
        {
            get
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                return (h == null || h[BudgetYearKey] == null) ? DefaultBudgetYear : (int)h[BudgetYearKey];
            }

            set
            {
                Hashtable h = pageCachePolicy.GetCachedObject(Keys.BudgetInfoPageKey) as Hashtable;
                if (h == null) h = new Hashtable();
                h[BudgetYearKey] = value;
                pageCachePolicy.Cache(Keys.BudgetInfoPageKey, h);
            }
        }



        #region Salary details data retrieval methods starts here
        private PHSRAG.Insight21.Agreements.SalaryDistributions SalaryDistributions
        {
            get
            {


                CommonSearchContext searchContext = (CommonSearchContext)cachePolicy.GetCachedObject(CommonSearchContext.CacheKey);

                SearchCriteria searchCriteria = null;

                if (searchContext != null)
                {
                    searchCriteria = searchContext.GetSearchCriteria(pageUrl);
                }
                else
                    throw (new Exception("Session Expired, Please Logout and Log In again"));
                //int searchVersion = searchCriteria.Version;

                PHSRAG.Insight21.Agreements.SalaryDistributions salaryDistributions = null;

                if (salaryDistributions == null)
                {
                    salaryDistributions = new PHSRAG.Insight21.Agreements.SalaryDistributions();

                    bool ignoreFundStatus = false;
                    bool ignoreAgreementStatus = false;

                    string strConn = Utility.DataAccess.ConnectionString.ToString();

                    string fundNumber = (RelatedFundNumber.Equals(string.Empty)) ? FundNumber : RelatedFundNumber;

                    salaryDistributions.Load(
                        strConn, Session.SessionID
                        , searchCriteria, glPostAsOfDate.ToShortDateString()
                        , fundNumber, ignoreFundStatus, ignoreAgreementStatus, IsDetail, CumLevel, BudgetYear);


                    //SearchCriteriaVersion = searchVersion;

                    // Policy.ResultsCache.SetObject(cachePolicy, Agreements.SalaryDistributions.CacheKey, salaryDistributions);
                }

                return salaryDistributions;
            }
        }

        #endregion 

    }
}
